package com.munichosica.myapp.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import com.munichosica.myapp.dao.PersonaDao;
import com.munichosica.myapp.dto.Persona;
import com.munichosica.myapp.exceptions.PersonaDaoException;

public class PersonaDaoImpl implements PersonaDao{

	private StringBuilder sql;
	private StringBuilder sqlSelect;
	
	public PersonaDaoImpl() {
		sql=new StringBuilder();
		sqlSelect=new StringBuilder();
		sqlSelect.append("SELECT PERCODIGO_I AS CODIGO, PERDNI_V AS DNI, " +
				"PERNOMBRES_V AS NOMBRES, PERPATERNO_V AS PATERNO, PERMATERNO_V AS MATERNO, " +
				"DATE_FORMAT(PERNACIMIENTO_F,'%d/%m/%Y') AS NACIMIENTO, PERESTADOCIVIL_C AS ESTADOCIVIL, " +
				"PERMOVILCLA_V AS CLARO, PERMOVILMOV_V AS MOVISTAR, PERMOVILNEX_V AS NEXTEL, " +
				"PERTELEFFIJO_V AS TELEFONO, PERCORREO_V AS CORREO, " +
				"PERDOMICILIO_V AS DOMICILIO, PERUBDEPT_C AS DEPARTAMENTO, " +
				"IFNULL(FNC_GET_UBIGEONOMBRE(PERUBDEPT_C),'') AS DEP_NOMBRE, " +
				"PERUBPROV_C AS PROVINCIA, " +
				"IFNULL(FNC_GET_UBIGEONOMBRE(PERUBPROV_C),'') AS PRO_NOMBRE, " +
				"PERUBDIST_C AS DISTRITO, IFNULL(FNC_GET_UBIGEONOMBRE(PERUBDIST_C),'') AS DIS_NOMBRE," +
				"PERSEXO_C AS SEXO, PERESTADO_C AS ESTADO " +
				"FROM PERSONA ");
	}
	
	@Override
	public Persona insertar(Persona persona) throws PersonaDaoException {		
		sql.append("INSERT INTO PERSONA (PERDNI_V, PERNOMBRES_V, PERPATERNO_V, PERMATERNO_V, " +
				"PERNACIMIENTO_F,PERESTADOCIVIL_C, PERMOVILCLA_V, PERMOVILMOV_V, PERMOVILNEX_V, PERTELEFFIJO_V, " +
				"PERCORREO_V, PERDOMICILIO_V, PERUBDEPT_C, PERUBPROV_C, PERUBDIST_C, PERSEXO_C, " +
				"PERESTADO_C, PERUSUARIOREGISTRA_V,PERFECHAREGISTRA_F,PERIP_V) " +
				"VALUES (?, ?, ?, ?," +
				"STR_TO_DATE(?, '%d/%m/%Y'),?, ?, ?, ?, ?, " +
				"?, ?, ?, ?, ?, ?, " +
				"'A',?,CURDATE(),?)");
		Connection conn=null;
		PreparedStatement stmt=null;
		ResultSet rs=null;
		try {
			conn=ResourceManager.getConnection();
			stmt=conn.prepareStatement(sql.toString(),PreparedStatement.RETURN_GENERATED_KEYS);
			stmt.setString(1, persona.getPerdniV());
			stmt.setString(2, persona.getPernombresV());
			stmt.setString(3, persona.getPerpaternoV());
			stmt.setString(4, persona.getPermaternoV());
			stmt.setString(5, persona.getPernacimientoF());
			stmt.setString(6, persona.getPerestadocivilC());
			stmt.setString(7, persona.getPermovilclaV());
			stmt.setString(8, persona.getPermovilmovV());
			stmt.setString(9, persona.getPermovilnexV());
			stmt.setString(10, persona.getPerteleffijoV());
			stmt.setString(11, persona.getPeremailV());
			stmt.setString(12, persona.getPerdomicilioV());
			stmt.setString(13, persona.getPerubdptoV());
			stmt.setString(14, persona.getPerubprovV());
			stmt.setString(15, persona.getPerubidistV());
			stmt.setString(16, persona.getPersexoC());
			stmt.setString(17, persona.getPerusuarioV());
			stmt.setString(18, persona.getPeripV());
			int results=stmt.executeUpdate();
			if(results>0){
				rs=stmt.getGeneratedKeys();
				if(rs.next()){
					persona.setPercodigoD(rs.getLong(1));
				}
			}
		} catch (SQLException e) {
			throw new PersonaDaoException(e.getMessage(),e);
		} finally{
			ResourceManager.close(rs);
			ResourceManager.close(stmt);
			ResourceManager.close(conn);
		}
		return persona;
	}

	@Override
	public Persona modificar(Persona persona) throws PersonaDaoException {
		sql.append("UPDATE PERSONA SET PERDNI_V=?, " +
				"PERNOMBRES_V=?, PERPATERNO_V=?, PERMATERNO_V=?, " +
				"PERNACIMIENTO_F=STR_TO_DATE(?, '%d/%m/%Y'), PERESTADOCIVIL_C=?, " +
				"PERMOVILCLA_V=?, PERMOVILMOV_V=?, PERMOVILNEX_V=?, " +
				"PERTELEFFIJO_V=?, PERCORREO_V=?, PERDOMICILIO_V=?, " +
				"PERUBDEPT_C=?, PERUBPROV_C=?, PERUBDIST_C=?, " +
				"PERSEXO_C=?, PERUSUARIOACTUALIZA_V=?, " +
				"PERFECHAACTUALIZA_F=CURDATE(), PERIP_V=? " +
				"WHERE PERCODIGO_I=?");
		Connection conn=null;
		PreparedStatement stmt=null;
		ResultSet rs=null;
		try {
			conn=ResourceManager.getConnection();
			stmt=conn.prepareStatement(sql.toString(),Statement.RETURN_GENERATED_KEYS);
			stmt.setString(1, persona.getPerdniV());
			stmt.setString(2, persona.getPernombresV());
			stmt.setString(3, persona.getPerpaternoV());
			stmt.setString(4, persona.getPermaternoV());
			stmt.setString(5, persona.getPernacimientoF());
			stmt.setString(6, persona.getPerestadocivilC());
			stmt.setString(7, persona.getPermovilclaV());
			stmt.setString(8, persona.getPermovilmovV());
			stmt.setString(9, persona.getPermovilnexV());
			stmt.setString(10, persona.getPerteleffijoV());
			stmt.setString(11, persona.getPeremailV());
			stmt.setString(12, persona.getPerdomicilioV());
			stmt.setString(13, persona.getPerubdptoV());
			stmt.setString(14, persona.getPerubprovV());
			stmt.setString(15, persona.getPerubidistV());
			stmt.setString(16, persona.getPersexoC());
			stmt.setString(17, persona.getPerusuarioV());
			stmt.setString(18, persona.getPeripV());
			stmt.setLong(19, persona.getPercodigoD());
			boolean results=stmt.execute();
			if(results){
				rs=stmt.getGeneratedKeys();
				rs.next();
				persona.setPercodigoD(rs.getLong(1));
			}
		} catch (SQLException e) {
			throw new PersonaDaoException(e.getMessage(),e);
		} finally{
			ResourceManager.close(rs);
			ResourceManager.close(stmt);
			ResourceManager.close(conn);
		}
		return persona;
	}

	@Override
	public Persona obtener(Long codigo) throws PersonaDaoException {
		sqlSelect.append("WHERE PERCODIGO_I=?");
		Connection conn=null;
		PreparedStatement stmt=null;
		ResultSet rs=null;
		Persona persona=null;
		try {
			conn=ResourceManager.getConnection();
			stmt=conn.prepareStatement(sqlSelect.toString());
			stmt.setLong(1, codigo);
			boolean results=stmt.execute();
			if(results){
				rs=stmt.getResultSet();
				if(rs.next()){
					persona=new Persona();
					persona.setPercodigoD(rs.getLong("CODIGO"));
					persona.setPerdniV(rs.getString("DNI"));
					persona.setPernombresV(rs.getString("NOMBRES"));
					persona.setPerpaternoV(rs.getString("PATERNO"));
					persona.setPermaternoV(rs.getString("MATERNO"));
					persona.setPernacimientoF(rs.getString("NACIMIENTO"));
					persona.setPerestadocivilC(rs.getString("ESTADOCIVIL"));
					persona.setPermovilclaV(rs.getString("CLARO"));
					persona.setPermovilmovV(rs.getString("MOVISTAR"));
					persona.setPermovilnexV(rs.getString("NEXTEL"));
					persona.setPerteleffijoV(rs.getString("TELEFONO"));
					persona.setPeremailV(rs.getString("CORREO"));
					persona.setPerdomicilioV(rs.getString("DOMICILIO"));
					persona.setPerubdptoV(rs.getString("DEPARTAMENTO"));
					persona.setPerubdptonombreV(rs.getString("DEP_NOMBRE"));
					persona.setPerubprovV(rs.getString("PROVINCIA"));
					persona.setPerubprovnombreV(rs.getString("PRO_NOMBRE"));
					persona.setPerubidistV(rs.getString("DISTRITO"));
					persona.setPerubidistnombreV(rs.getString("DIS_NOMBRE"));
					persona.setPersexoC(rs.getString("SEXO"));
					persona.setPerestadoC(rs.getString("ESTADO"));
				}
			}
		} catch (SQLException e) {
			throw new PersonaDaoException(e.getMessage(),e);
		} finally{
			ResourceManager.close(rs);
			ResourceManager.close(stmt);
			ResourceManager.close(conn);
		}
		return persona;
	}

	@Override
	public void eliminar(Persona persona) throws PersonaDaoException {
		sql.append("UPDATE PERSONA SET PERESTADO_C='I', " +
				"PERUSUARIOACTUALIZA_V=?, " +
				"PERFECHAACTUALIZA_F=CURDATE()," +
				"PERIP_V=? " +
				"WHERE PERCODIGO_I=?");
		Connection conn=null;
		PreparedStatement stmt=null;
		try {
			conn=ResourceManager.getConnection();
			stmt=conn.prepareStatement(sql.toString());
			stmt.setString(1, persona.getPerusuarioV());
			stmt.setString(2, persona.getPeripV());
			stmt.setLong(3, persona.getPercodigoD());
			stmt.executeUpdate();
		} catch (SQLException e) {
			throw new PersonaDaoException(e.getMessage(),e);
		} finally{
			ResourceManager.close(stmt);
			ResourceManager.close(conn);
		}
	}

	@Override
	public boolean existeDniPersona(String dni) throws PersonaDaoException {
		sql.append("SELECT COUNT(*) CANTIDAD FROM PERSONA WHERE PERDNI_V=?");
		Connection conn=null;
		PreparedStatement stmt=null;
		ResultSet rs=null;
		try {
			conn=ResourceManager.getConnection();
			stmt=conn.prepareStatement(sql.toString());
			stmt.setString(1, dni);
			boolean results=stmt.execute();
			if(results){
				rs=stmt.getResultSet();
				if(rs.next()){
					int count=rs.getInt("CANTIDAD");
					if(count>0){
						return true;
					}
				}
			}
			
		} catch (SQLException e) {
			throw new PersonaDaoException(e.getMessage(),e);
		} finally{
			ResourceManager.close(rs);
			ResourceManager.close(stmt);
			ResourceManager.close(conn);
		}
		
		return false;
	}

	@Override
	public Persona obtenerPorDni(String dni) throws PersonaDaoException {
		sqlSelect.append("WHERE PERDNI_V=?");
		Connection conn=null;
		PreparedStatement stmt=null;
		ResultSet rs=null;
		Persona persona=null;
		try {
			conn=ResourceManager.getConnection();
			stmt=conn.prepareStatement(sqlSelect.toString());
			stmt.setString(1, dni);
			boolean results=stmt.execute();
			if(results){
				rs=stmt.getResultSet();
				if(rs.next()){
					persona=new Persona();
					persona.setPercodigoD(rs.getLong("CODIGO"));
					persona.setPerdniV(rs.getString("DNI"));
					persona.setPernombresV(rs.getString("NOMBRES"));
					persona.setPerpaternoV(rs.getString("PATERNO"));
					persona.setPermaternoV(rs.getString("MATERNO"));
					persona.setPernacimientoF(rs.getString("NACIMIENTO"));
					persona.setPerestadocivilC(rs.getString("ESTADOCIVIL"));
					persona.setPermovilclaV(rs.getString("CLARO"));
					persona.setPermovilmovV(rs.getString("MOVISTAR"));
					persona.setPermovilnexV(rs.getString("NEXTEL"));
					persona.setPerteleffijoV(rs.getString("TELEFONO"));
					persona.setPeremailV(rs.getString("CORREO"));
					persona.setPerdomicilioV(rs.getString("DOMICILIO"));
					persona.setPerubdptoV(rs.getString("DEPARTAMENTO"));
					persona.setPerubdptonombreV(rs.getString("DEP_NOMBRE"));
					persona.setPerubprovV(rs.getString("PROVINCIA"));
					persona.setPerubprovnombreV(rs.getString("PRO_NOMBRE"));
					persona.setPerubidistV(rs.getString("DISTRITO"));
					persona.setPerubidistnombreV(rs.getString("DIS_NOMBRE"));
					persona.setPersexoC(rs.getString("SEXO"));
					persona.setPerestadoC(rs.getString("ESTADO"));
				}
			}
		} catch (SQLException e) {
			throw new PersonaDaoException(e.getMessage(),e);
		} finally{
			ResourceManager.close(rs);
			ResourceManager.close(stmt);
			ResourceManager.close(conn);
		}
		return persona;
	}

}
